﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using DataHelper;
namespace DAL
{
    public class DepartmentDao
    {

        /// <summary>
        /// 根据SQL语句，获取字典列表
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataTable GetLists(string strSQL)
        {
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }

        public DataSet GetDataSet(string strSQL)
        {
            return SQLHelper.ExecuteSqlGetDataSet(strSQL);
        }
        public DataSet GetDataSet(string procName, SqlParameter[] spList)
        {
            return SQLHelper.ExecutePorcedureGetDataSet(procName, spList);
        }


        public bool ExecSQL(string strSQL)
        {
            return SQLHelper.ExecuteSqlWithTransaction(strSQL);
        }
        //获取所有公司ID和名字
        public DataTable GetCompany()
        {
            string sql = "select DeptID as ID,Name from Admin_Department where Propertie like '%Company'";
            return SQLHelper.ExecuteSqlGetDrToDt(sql);
        }
        //获取部门编号  根据部门ID
        public string GetDeptCodeByID(string deptID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDataSet("select DeptCode from Admin_Department where DeptID=" + deptID).Tables[0];
            if (dt == null) return "";
            if (dt.Rows.Count < 1) return "";
            return dt.Rows[0]["DeptCode"].ToString();
        }
        //根据部门ID 获取父部门的编码 不是ID
        public string GetParentDeptCode(string deptID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("select DeptCode from Admin_Department where DeptID=" + deptID);
            if (dt == null) return "";
            if (dt.Rows.Count < 1) return "";
            else if (dt.Rows[0]["DeptCode"].ToString().Length < 3) return dt.Rows[0]["DeptCode"].ToString();
            else
                return dt.Rows[0]["DeptCode"].ToString().Substring(0, dt.Rows[0]["DeptCode"].ToString().Length - 2);

        }
        //判断是否有子部门
        public bool IsHaveSubDept(string deptID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("select DeptCode from Admin_Department where ParentID=" + deptID);
            if (dt == null) return false;
            if (dt.Rows.Count < 1) return false;
            else return true;

        }

        //来提取部门结构的DataTable
        //
        public DataTable GetRightDept(string account, string companyID, string departmentID, string url, out string parentID)
        {

            string deptCode = GetDeptCodeByID(departmentID);
            if (string.IsNullOrWhiteSpace(deptCode)) { parentID = "-2"; return null; }
            if (deptCode.Length < 2) { parentID = "-2"; return null; }
            //string comCode = GetDeptCodeByID(companyID);
            string sql = "select ParentID,DeptID,DeptCode,Name from Admin_Department Where State='Y' ";
            string rootID = "";
            string filter = "";
            int rangeChar = (int)AccountDao.GetRange(account, "~" + url);
            switch (rangeChar)
            {
                case 1:
                case 6:
                case 7:
                case 2:
                    filter = " and DeptCode like '" + deptCode + "%' ";
                    rootID = departmentID;
                    break;
                case 3:
                    filter = " and DeptCode like '" + deptCode.Substring(0, 2) + "%' ";
                    rootID = departmentID;
                    break;
                case 4:
                case 5:

                    filter = "";
                    rootID = "0";
                    break;
            }

            if (!string.IsNullOrEmpty(sql))
            {
                sql += filter + " order by AbsIndex ASC ";
            }
            parentID = rootID;
            if (string.IsNullOrWhiteSpace(sql)) return null;
            else
            {
                DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(sql);
                return dt;
            }
        }

        //根据部门 和账号，查询信息使用
        //一般是查询列表的信息
        public string FilterDept(string comID, string deptID, string account, string url)
        {
            string filter = "";
            if (AccountDao.IsSuperGroup(account)) return filter;
            string deptCode = GetDeptCodeByID(deptID); //获取部门编码   
            int rangeChar = (int)AccountDao.GetRange(account, "~" + url);
            switch (rangeChar)
            {
                case 1://自己的
                case 6://限制
                    filter = " and  Account='" + account + "'";
                    break;
                case 2://显示部门
                    filter = " and  DeptCode like '" + deptCode + "%'";
                    break;
                case 3://公司的
                    filter = " and  DeptCode like '" + deptCode.Substring(0, 2) + "%'";
                    break;
                case 4://集团的
                case 5://超级用户
                    break;
                case 7:
                    filter = " and 1=2";
                    break;
                default:
                    filter = " and  Account='" + account + "'";
                    break;
            }

            return filter;
        }

        //获取一个部门下 所有子部门的列表
        public string GetDepartmentIDArray(string deptID)
        {
            string strSQL = "select DepartmentID,ParentID from Admin_Department order by AbsIndex DESC";
            DataSet ds = SQLHelper.ExecuteSqlGetDataSet(strSQL);
            //定义公司下面所有部门的ID组成的字符串
            string strDeptIDArray = deptID.ToString() + ",";
            if (ds != null)
            {
                //this.ViewState["ds"] = ds;
                strDeptIDArray += GetSubID(ds, deptID);
            }
            //return strDeptIDArray;
            return strDeptIDArray.Remove(strDeptIDArray.ToString().Length - 1);
        }
        //递归获得部门下所有子部门的ID
        protected string GetSubID(DataSet ds, string ParentID)
        {
            string strDeptIDArray = "";
            DataView dvData = new DataView(ds.Tables[0]);
            //过滤ParentID
            dvData.RowFilter = "[ParentID] = " + ParentID;
            foreach (DataRowView Row in dvData)
            {
                strDeptIDArray += Row["DepartmentID"].ToString() + ",";
                strDeptIDArray += GetSubID(ds, Row["DepartmentID"].ToString());
            }
            return strDeptIDArray;
        }

        /*
       获得该帐号的上级部门ID或公司ID
       Int32 iDept 该帐号所在的部门ID
       Int32 dLevel 该帐号所在的部门的level
       Int32 iLevel  想获得哪一层的ID
       如果ilevel=1 则要获得该帐号所在公司的ID
       如果ilevel=dLevel 该帐号所在的部门的上一级部门
       如果ilevel=dLevel-1 该帐号所在的部门的上两级部门
       如果ilevel=dLevel-2 该帐号所在的部门的上三级部门
       以此类推
        * 所以要使用此函数，一定要知道该帐号所在的部门，以及此部门的level
        */
        public int GetPreDepartmentID(int deptID, int level)
        {

            SqlParameter para = new SqlParameter("@DepartmentID", SqlDbType.Int);
            SqlParameter para1 = new SqlParameter("@ItemLevel", SqlDbType.Int);
            para.Value = deptID;// iDepartmentID;
            para1.Value = level;

            SqlParameter[] spList = { para, para1 };
            DataTable mydt = SQLHelper.ExecutePorcedureGetDataSet("sp_JimProc_GetComID2", spList).Tables[0];
            return Int32.Parse(mydt.Rows[0][0].ToString());//目标ID
        }
    } 
}
